Merveilleux SQL!

PGSessions de Dalibo
2019-11-21

Qui suis-je ?

• mydbanotebook.org
• Lætitia Avrot (@l_avrot)
• Membre du CoC PostgreSQL
• Co-fondatrice de #PostgresWomen
• Consultante EnterpriseDB

Les merveilles méconnues du SQL

Différent

• Langage déclaratif
• Avec indentation
• Avec retours à la ligne
• Avec commentaires

Turing complete!

Démonstration sur le wiki de PostgreSQL: https://wiki.postgresql.org/wiki/
Turing_Machine_(with_recursive)

Advent of code 2017: https://github.com/xocolatl/advent-of-code/tree/master/2017

Historique

• SQL-86
• SQL-89 annule et remplace SQL-86
• SQL-92
• SQL:1999, SQL:2003, SQL:2006
• SQL:2008, SQL:2011, SQL:2013
• SQL:2016

pgexercises.com

Sous-requête

select
  mem."firstname"
    || ' ' ||
    mem."surname",
  (
    select
      memref."firstname"
        || ' ' ||
        memref."surname"
    from members as memref
    where
      mem."recommendedby" = memref."memid"
  )
from members as mem

Sous-requête

(dans la clause select)

• Affiche directement le résultat d'une sous-requête
• Uniquement pour les sous-requêtes scalaires
• SQL:1999

CTE

with memref as
(
  select
    "firstname" || ' ' || "surname" as refname
  from
    members
)
select
  mem."firstname" || ' ' || mem."surname",
  memref.refname
from
  members as mem
left outer join memref
on mem."recommendedby" = memref."memid"

Common Table Expressions (CTE)

• Permet de déclarer des sous-requêtes
• Clause with
• SQL:1999

select
  mem."firstname"
    || ' ' ||
    mem."surname",
  memref."firstname"
    || ' ' ||
    memref."surname"
from
  "members" as mem
left outer join
  "members" as memref
on mem."recommendedby" = memref."memid"

select
  mem."firstname",
  mem."surname",
  (
    select
      memref."firstname",
      memref."surname",
    from members as memref
    where
      mem."recommendedby" = memref."memid"
)
from members as mem

Jointure Laterale

select
  mem."firstname",
  mem."surname",
    memref."firstname",
  memref."surname"
from members as mem
left join lateral
  (select "firstname",
     "surname"
   from members as mentors
   where
     mentors.memid = mem.recommendedby) as memref
on true

Jointure Laterale

• À utiliser lorsqu'on pense "pour chaque ligne, je voudrais ..."
• Attention à l'addiction!
• Depuis SQL:1999

In

select
  "firstname",
  "surname"
from members
where
  "firstname" in ('David', 'Tim', 'Darren')

Values

          insert into ("column1",...) values (...);

          values ('Hello world');

Values

select
  "firstname",
  "surname"
from members
inner join
  values
    (('David'),('Tim'),('Darren'))
  as people(firstname)
on members."firstname" = people."firstname"

Values

• Pour créer une table de constantes
• Réduire le trafic réseau lors des inserts
• Optimiser certaines requêtes in
• Depuis SQL:92

NOT IN

select surname,
  firstname
from members
where "memid" not in
  (
    select memid
    from bookings
  )

NOT EXISTS

select surname,
  firstname
from members
where not exists
  (
    select 1
    from bookings
    where bookings.memid = members.memid
  )

ANTI-JOIN

select surname,
  firstname
from members
left outer join "bookings"
on "members"."memid" = "bookings"."memid"
where "bookings".memid is null

Rollup

select facid,
  extract(month from starttime),
  sum(slots) as slots
from cd.bookings
where extract(year from starttime) = 2012
group by rollup("facid", extract(month from "starttime"))
order by "facid",
  extract(month from "starttime")

Rollup

• Données hiérarchisées
• Différent de CUBE
• Depuis SQL:99

Sous-requête

select (
  select count(*) from members
) as count,
firstname, surname
        from members
order by joindate

Window functions

select count(*) 
  over(),
  "firstname",
  "surname"
from members
order by joindate 

Window functions

• Aggrégats en dehors des colonnes affichées
• SQL:2003
• SQL:2011: LEAD, LAG...